VBA Function: InStr

Description

The VBA INSTR Function returns an integer corresponding to the first position found of a value in a character string (or the value 0 if no match was found).

INSTR Syntax

InStr(start_num, text, value)

Or

InStr(start_num, text, value, case)

VBA InStr Example

Using the InStr Function to determine an "excel" word position (starting searches from the character 1 of the sitename):

Sub InStrExample1()
    
    sitename = "www.moonexcel.com.ua"
    
    'Position of "excel" in the sitename
    position = InStr(1, sitename, "excel")
    
    MsgBox position 'Returns: 9
    
End Sub

Using the InStr Function to determine an "EXCEL" word position (this time adding the value 1 in the 4th argument to ignore case):

Sub InStrExample2()
    
    sitename = "www.moonexcel.com.ua"
    
    'Position of "EXCEL" in the sitename (ignoring case)
    position = InStr(1, sitename, "EXCEL", 1)
    
    MsgBox position 'Returns: 9
    
End Sub

CHECK IF A TEXT CONTAINS A VALUE

The InStr Function can also be used to determine whether or not the sitename contains the search string:

Sub InStrExample3()
    
    sitename = "www.moonexcel.com.ua"
    
    If InStr(1, sitename, "excel") > 0 Then
      MsgBox "Yes!"
    End If
    
End Sub

In this example, if a position was found, a number greater than 0 is returned by the function and the dialog box is displayed.